﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
using Excel=Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Threading;

namespace DeloUtils
{
    public partial class frmMain : Form
    {
        private frmConnect fConnect;
        private frmApiConnect fApiConnect;
        public OleDbConnection connection;
        public EApi.Head ApiConnection;
        private Excel.Application appExcel;
        private Excel.Workbook ExcelWorkbook;
        private OleDbCommand cmdDepartment;
        private Dictionary<int,Int32> DepartmentList = new Dictionary<int,Int32>();
        private Dictionary<Int32, string> DepartmentVertex = new Dictionary<Int32, string>();
        private List<DepartmentCor> DCor = new List<DepartmentCor>();
        public string SqlPrefix = "";
        

        public frmMain()
        {
            InitializeComponent();
            fConnect = new frmConnect();
            fApiConnect = new frmApiConnect();
            //connection = new OleDbConnection();
            //ApiConnection = Activator.CreateInstance<EApi.Head>();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            fConnect.ShowDialog(this);
        }

        private void tmrCheckConnection_Tick(object sender, EventArgs e)
        {
            if ((connection != null) && (connection.State != ConnectionState.Closed))
            {
                sslConnection.Text = "Подключенно";
                if ((lsbDepartmentsList.Items.Count==0) && (DepartmentVertex.Count==0))
                {
                    cmdDepartment = new OleDbCommand("SELECT ISN_NODE,CLASSIF_NAME FROM "+SqlPrefix+"DEPARTMENT WHERE (ISN_HIGH_NODE=0) AND (IS_NODE=0) ORDER BY WEIGHT", connection);
                    OleDbDataReader drDepartment=cmdDepartment.ExecuteReader();
                    int i;
                    DepartmentList.Clear();
                    while (drDepartment.Read())
                    {
                        i=lsbDepartmentsList.Items.Add(drDepartment[1]);
                        DepartmentList.Add(i, Int32.Parse(drDepartment[0].ToString()));
                    }
                    drDepartment.Close();
                }
                string version = Migra.ExportDocuments.GetVersion(connection);
                if (version!="")
                {
                    sslVersion.Text = "Версия: " + version; 
                }
                else
                {
                    sslVersion.Text = "Версия: неизвестно";
                }
            }
            else
            {
                sslConnection.Text = "Отключенно";
            }
            if ((ApiConnection != null) && (ApiConnection.Active))
            {
                sslApiConnection.Text = "Api Подключенно";
            }
            else
            {
                sslApiConnection.Text = "Api Отключенно";
            }
        }

        private void btnOpenDepartment_Click(object sender, EventArgs e)
        {
            OpenFileDialog fd = new OpenFileDialog();
            fd.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx";
            fd.SupportMultiDottedExtensions = true;
            if (fd.ShowDialog() == DialogResult.OK)
            {
                txtFileDepartment.Text = fd.FileName;
                appExcel = new Excel.Application();
                ExcelWorkbook=appExcel.Workbooks.Open(fd.FileName);
            }
        }

        private void btnLoadDepartment_Click(object sender, EventArgs e)
        {
            if ((connection == null) || (connection.State == ConnectionState.Closed))
            {
                MessageBox.Show(this, "База не подключенна", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }
            if (txtFileDepartment.Text == "")
            {
                MessageBox.Show(this, "Книга для загрузки не выбрана", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }
            
            Excel.Worksheet wrkSheet = CheckDepartment();
            if (wrkSheet != null)
            {
                DCor.Clear();
                txtDepartmentLoadStatus.Text += "Страница с подразделениями найдена\r\n";
                progressBar1.Maximum = RowCNT(wrkSheet);
                progressBar1.Value = 0;
                txtDepartmentLoadStatus.Text += "Подсчитанно кол-во елементов\r\n";
                Int32 dbISN = 0;
                if (DepartmentVertex.Count > 0) dbISN = DepartmentVertex.Keys.ElementAt(DepartmentVertex.Count - 1);
                txtDepartmentLoadStatus.Text += "Найдена вершина в базе данных:"+dbISN.ToString()+"\r\n";
                Int32 minisn = MinISNH(wrkSheet, 0);
                txtDepartmentLoadStatus.Text += "Найдена вершина в таблице:" + minisn.ToString() + "\r\n";
                List<Int32> xlsISN = ChildDepartments(wrkSheet, minisn);
                int i;
                for (i = 0; i < xlsISN.Count; i++)
                {
                    DepartmentCor _dcor = new DepartmentCor();
                    _dcor.xlsISN = xlsISN[i];
                    _dcor.xlsISN_HIGH = minisn;
                    _dcor.dbISN_HIGH = dbISN;
                    DCor.Add(_dcor);
                }
                i = 0;
                int row;
                txtDepartmentLoadStatus.Text += "Загрузка данных\r\n";
                while (DCor.Count > i)
                {
                    row = FindRow(wrkSheet, DCor[i].xlsISN);

                    DCor[i].dbISN = ReadAndAppendRow(wrkSheet, row, DCor[i].dbISN_HIGH);
                    progressBar1.Value += 1;

                    xlsISN.Clear();
                    xlsISN = ChildDepartments(wrkSheet, DCor[i].xlsISN);
                    for (int k = 0; k < xlsISN.Count; k++)
                    {
                        DepartmentCor _dcor = new DepartmentCor();
                        _dcor.xlsISN = xlsISN[k];
                        _dcor.xlsISN_HIGH = minisn;
                        _dcor.dbISN_HIGH = DCor[i].dbISN;
                        DCor.Add(_dcor);
                    }
                    i++;
                }
                progressBar1.Value = progressBar1.Maximum;
                txtDepartmentLoadStatus.Text += "Загрузка завершенна! " + DCor.Count.ToString() + " - подразделений\r\n";
            }
            else
            {
                MessageBox.Show(this, "Подразделений для загрузки не обнаруженно", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return;
            }
        }

        private Excel.Worksheet CheckDepartment()
        {
            Excel.Worksheet _;
            for (int i = 1; i <= ExcelWorkbook.Sheets.Count; i++)
			{
                _ = (Excel.Worksheet)ExcelWorkbook.Sheets[i];
                if (_.Name=="DEPARTME")
                {
                    return _;
                }
			}
            return null;
        }

        private void lsbDepartmentsList_DoubleClick(object sender, EventArgs e)
        {
            int i = lsbDepartmentsList.SelectedIndex;
            if (i == -1) return;
            Int32 ISN = DepartmentList[lsbDepartmentsList.SelectedIndex];
            String NAME=lsbDepartmentsList.Items[i].ToString();
            DepartmentVertex.Add(ISN,NAME);
            cmdDepartment.CommandText = "SELECT ISN_NODE,CLASSIF_NAME FROM " + SqlPrefix + 
                "DEPARTMENT WHERE (ISN_HIGH_NODE=" + ISN.ToString() + ") AND (IS_NODE=0) ORDER BY WEIGHT";
            OleDbDataReader drDepartment=cmdDepartment.ExecuteReader();
            DepartmentList.Clear();
            lsbDepartmentsList.Items.Clear();
            while (drDepartment.Read())
            {
                i=lsbDepartmentsList.Items.Add(drDepartment[1]);
                DepartmentList.Add(i, Int32.Parse(drDepartment[0].ToString()));
            }
            drDepartment.Close();
            txtPathVertexDepartment.Text = "";
            for (int j = 0; j < DepartmentVertex.Count; j++)
            {
                txtPathVertexDepartment.Text += "/" + DepartmentVertex.Values.ElementAt(j).ToString();
            }
        }

        private void txtPathVertexDepartment_Click(object sender, EventArgs e)
        {
            if (txtPathVertexDepartment.Text == "") return;
            Int32 ISN = DepartmentVertex.Keys.ElementAt(DepartmentVertex.Count-1);
            DepartmentVertex.Remove(ISN);
            if (DepartmentVertex.Count > 0)
            {
                ISN = DepartmentVertex.Keys.ElementAt(DepartmentVertex.Count - 1);
            }
            else ISN = 0;
            cmdDepartment.CommandText = "SELECT ISN_NODE,CLASSIF_NAME FROM " + SqlPrefix + "DEPARTMENT WHERE (ISN_HIGH_NODE=" + ISN.ToString() + ") AND (IS_NODE=0) ORDER BY WEIGHT";
            OleDbDataReader drDepartment=cmdDepartment.ExecuteReader();
            DepartmentList.Clear();
            lsbDepartmentsList.Items.Clear();
            int i;
            while (drDepartment.Read())
            {
                i=lsbDepartmentsList.Items.Add(drDepartment[1]);
                DepartmentList.Add(i, Int32.Parse(drDepartment[0].ToString()));
            }
            drDepartment.Close();
            txtPathVertexDepartment.Text = "";
            for (int j = 0; j < DepartmentVertex.Count; j++)
            {
                txtPathVertexDepartment.Text += "/" + DepartmentVertex.Values.ElementAt(j).ToString();
            }
        }

        private Int32 AppendRow(Int32 ISN_HIGH, Int16 IS_NODE, Int32 WEIGHT, String NAME, 
            String SURNAME, String DUTY, Int16 DELETED, String DEP_INDEX, Int16 POST_H, String NOTES)
        {
            OleDbCommand ExecCmd = new OleDbCommand();
            ExecCmd.Connection = connection;
            ExecCmd.CommandType = CommandType.StoredProcedure;
            ExecCmd.CommandText = "GET_SEQ";
            ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
            ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
            ExecCmd.Parameters.Add("airet", OleDbType.Integer);
            ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
            ExecCmd.ExecuteNonQuery();
            Int32 ISN = (Int32)(ExecCmd.Parameters["airet"].Value);

            if (connection.Provider.ToLower().IndexOf("ora") > -1)
            {
                ExecCmd.CommandText = "SELECT DISTINCT Base36(" + ISN.ToString() + ") FROM WU";
            }
            else
            {
                ExecCmd.CommandText = "SELECT dbo.Base36(" + ISN.ToString() + ")";
            }
            ExecCmd.Parameters.Clear();
            ExecCmd.CommandType = CommandType.Text;
            OleDbDataReader dr = ExecCmd.ExecuteReader();
            if (!dr.Read()) return 0;
            String DUE = dr[0].ToString();
            dr.Close();
            String DEPARTMENT_DUE = "0.";

            cmdDepartment.CommandText = "SELECT DUE,WEIGHT,LAYER FROM " + SqlPrefix + "DEPARTMENT WHERE ISN_NODE=" + ISN_HIGH;
            dr = cmdDepartment.ExecuteReader();
            Int32 _WEIGHT = 0;
            Int32 _LAYER = 0;
            if (dr.Read())
            {
                DEPARTMENT_DUE = dr[0].ToString();
                DUE = DEPARTMENT_DUE + DUE;
                _WEIGHT = Int32.Parse(dr[1].ToString()) + WEIGHT;
                _LAYER = Int32.Parse(dr[2].ToString())+1;
            }
            dr.Close();
            DEPARTMENT_DUE = "0.";
            String SQLCOMMAND = "insert  into " + SqlPrefix + "department (DUE,ISN_NODE,ISN_HIGH_NODE,LAYER,IS_NODE,WEIGHT,MAXDUE,";
		    SQLCOMMAND+="CLASSIF_NAME,SURNAME,DUTY,DEPARTMENT_DUE,PROTECTED,DELETED,";
            SQLCOMMAND+="DEPARTMENT_INDEX,POST_H,CARD_FLAG,CARD_NAME,NOTE";				
	        SQLCOMMAND+=") values ('"+DUE+"',"+ISN.ToString()+","+ISN_HIGH.ToString()+","+_LAYER+","+IS_NODE.ToString()+
            "," + _WEIGHT.ToString() + ",'" + DUE + "','" + NAME + "','" + SURNAME + "','" + DUTY + "','" + DEPARTMENT_DUE + 
            "',0," + DELETED.ToString() + ",'" + DEP_INDEX+"',"+POST_H.ToString()+",0,null,'"+NOTES+"')";

            cmdDepartment.CommandText = SQLCOMMAND;
            cmdDepartment.ExecuteNonQuery();

            return ISN;
        }

        private Int32 ReadAndAppendRow(Excel.Worksheet wrkSheet, Int32 Row, Int32 ISN_HIGH)
        {
            int CNTColumnIS_NODE = FindColumn(wrkSheet, "IS_NODE");
            int CNTColumnWEIGHT = FindColumn(wrkSheet, "WEIGHT");
            int CNTColumnNAME = FindColumn(wrkSheet, "NAME");
            int CNTColumnSURNAME = FindColumn(wrkSheet, "SURNAME");
            int CNTColumnDUTY = FindColumn(wrkSheet, "DUTY");
            int CNTColumnDELETED = FindColumn(wrkSheet, "DELETED");
            int CNTColumnDEP_INDEX = FindColumn(wrkSheet, "DEP_INDEX");
            int CNTColumnPOST_H = FindColumn(wrkSheet, "POST_H");
            int CNTColumnNOTES = FindColumn(wrkSheet, "NOTES");
            object _t;
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnIS_NODE])).Value;
            Int16 IS_NODE = (_t != null) ? (Int16.Parse(_t.ToString())) : (Int16)0;
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnWEIGHT])).Value;
            Int32 WEIGHT = (_t != null) ? (Int32.Parse(_t.ToString())) : (Int32)0;
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnNAME])).Value;
            String NAME = (_t != null) ? (_t.ToString()) : "";
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnSURNAME])).Value;
            String SURNAME = (_t != null) ? (_t.ToString()) : "";
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnDUTY])).Value;
            String DUTY = (_t != null) ? (_t.ToString()) : "";
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnDELETED])).Value;
            Int16 DELETED = (_t != null) ? (Int16.Parse(_t.ToString())) : (Int16)0;
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnDEP_INDEX])).Value;
            String DEP_INDEX = (_t != null) ? (_t.ToString()) : "";
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnPOST_H])).Value;
            Int16 POST_H = (_t != null) ? (Int16.Parse(_t.ToString())) : (Int16)0;
            _t = ((Excel.Range)(wrkSheet.Cells[Row, CNTColumnNOTES])).Value;
            String NOTES = (_t != null) ? (_t.ToString()) : "";

            return AppendRow(ISN_HIGH, IS_NODE, WEIGHT, NAME, SURNAME, DUTY, DELETED, DEP_INDEX, POST_H, NOTES);
        }

        private int MinISNH(Excel.Worksheet wrkSheet, Int32 ISNMIN)
        {
            int CNTColumn = FindColumn(wrkSheet,"ISN_HIGH");
            if (CNTColumn == 0) return 0;
            int _res=0;
            int _tmp=0;
            object _value = ((Excel.Range)(wrkSheet.Cells[2, CNTColumn])).Value;
            bool fl = true;
            for (int i = 2; _value != null; i++)
            {
                _tmp = Int32.Parse(_value.ToString());
                if (fl && (_tmp >= ISNMIN))
                {
                    _res = _tmp;
                    fl = false;
                }
                if ((_tmp >= ISNMIN) && (_tmp < _res)) _res = _tmp;
                _value = ((Excel.Range)(wrkSheet.Cells[i, CNTColumn])).Value;
            }
            return _res;
        }

        private int FindColumn(Excel.Worksheet wrkSheet, String name)
        {
            int i = 1;
            object _value = ((Excel.Range)(wrkSheet.Cells[1, i])).Value;
            while ((_value.ToString() != name) && (_value != null))
            {
                i++;
                _value = ((Excel.Range)(wrkSheet.Cells[1, i])).Value;
            }
            if (_value == null)
            {
                return 0;
            }
            else
            {
                return i;
            }
        }

        private int FindRow(Excel.Worksheet wrkSheet, Int32 isn)
        {
            int i = FindColumn(wrkSheet, "ISN");
            int j = 2;
            object _value = ((Excel.Range)(wrkSheet.Cells[j, i])).Value;
            while ((_value != null) && (Int32.Parse(_value.ToString()) != isn))
            {
                j++;
                _value = ((Excel.Range)(wrkSheet.Cells[j, i])).Value;
            }
            return j;
        }

        private int RowCNT(Excel.Worksheet wrkSheet)
        {
            int j = 1;
            object _value = ((Excel.Range)(wrkSheet.Cells[j, 1])).Value;
            while ((_value != null))
            {
                j++;
                _value = ((Excel.Range)(wrkSheet.Cells[j, 1])).Value;
            }
            return j-1;
        }

        private List<int> ChildDepartments(Excel.Worksheet wrkSheet, Int32 ISN_HIGH)
        {
            int CNTColumn = FindColumn(wrkSheet, "ISN_HIGH");
            int CNTColumnISN = FindColumn(wrkSheet, "ISN");
            if (CNTColumn == 0) return null;
            if (CNTColumnISN == 0) return null;
            int _tmp;
            int i = 2;
            object _value = ((Excel.Range)(wrkSheet.Cells[i, CNTColumn])).Value;
            object _valueISN;
            List<int> _res = new List<int>();
            while (_value != null)
            {
                _tmp = Int32.Parse(_value.ToString());
                if (_tmp == ISN_HIGH)
                {
                    _valueISN = ((Excel.Range)(wrkSheet.Cells[i, CNTColumnISN])).Value;
                    if (_valueISN != null) _res.Add(Int32.Parse(_valueISN.ToString()));
                }
                i++;
                _value = ((Excel.Range)(wrkSheet.Cells[i, CNTColumn])).Value;
            }
            return _res;
        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            if (ExcelWorkbook != null) ExcelWorkbook.Close();
            appExcel = null;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fd = new OpenFileDialog();
            fd.Filter = "XML Configuration BP (*.xml)|*.xml";
            fd.SupportMultiDottedExtensions = true;
            if (fd.ShowDialog() == DialogResult.OK)
            {
                txtFile.Text = fd.FileName;
                LoadTree(fd.FileName);
            }
        }

        // Загрузка дерева из файла конфигурации
        private void LoadTree(String FileName)
        {
            XmlDocument xmlconfig = new XmlDocument();
            //xmlConfiguration xmlconfiguration = new xmlConfiguration();
            xmlconfig.Load(FileName);
            trvConfiguration.Nodes.Clear();
            XmlNodeList GroupList = xmlconfig.SelectNodes("//OnEvent");
            if (GroupList.Count>0)
            {
                
            }
        }

        private DateTime StartExport;
        private Int32 CountDocs = 0;
        private void btnExport_Click(object sender, EventArgs e)
        {
            Migra.TModeLocalDB ldbOpen = Migra.TModeLocalDB.Exist;
            Migra.LocalDBCompact localdb = null;
            if (chbExportTables.Checked)
            {
                switch (cmbLocalDBOpen.SelectedIndex)
                {
                    case 0:
                        ldbOpen = Migra.TModeLocalDB.Create;
                        break;
                    case 1:
                    case 2:
                    case 3:
                        ldbOpen = Migra.TModeLocalDB.Exist;
                        break;                    
                    default:
                        MessageBox.Show("Укажите необходимость создание базы протоколов");
                        return;
                }
            }
            try
            {
                localdb = new Migra.LocalDBCompact(txtFolder.Text + "\\protocol.sdf", ldbOpen);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при подключению к протоколу: \r\n" + ex.Message);
            }
            StartExport = DateTime.Now;
            btnExport.Enabled = false;
            Migra.ExportDocuments.localdb = localdb;
            Migra.ExportTables.CurrentRows += Progress;
            Migra.ExportTables.CurrentMessage += ProgressMessage;
            Migra.ExportDocuments.CurrentRows += Progress;
            Migra.ExportTables.localdb = localdb;
            if (chbExportTables.Checked)
            {
                switch (cmbLocalDBOpen.SelectedIndex)
                {
                    case 2:
                        prbDocuments.Maximum = Migra.ExportTables.CountTask;
                        prbDocuments.Value = 0;
                        Migra.ExportTables.ExportAr(connection);
                        lblProgress.Text = "Состояние выгрузки: Дополнительные реквизиты выгружены!";
                        break;
                    case 3:
                        prbDocuments.Maximum = Migra.ExportTables.CountTask;
                        prbDocuments.Value = 0;
                        Migra.ExportTables.ExportProt(connection);
                        lblProgress.Text = "Состояние выгрузки: Протоколы выгружены!";
                        break;
                    default:
                        prbDocuments.Maximum = Migra.ExportTables.CountTask;
                        prbDocuments.Value = 0;
                        Migra.ExportTables.Execute(connection);
                        break;
                }
            }
            if (chbExportDocuments.Checked)
            {
                lblProgress.Text = "Состояние выгрузки: Выгрузка документов";
                prbDocuments.Value = 0;
                if (txtOnlyYear.Text != "") Migra.ExportDocuments.year = Int32.Parse(txtOnlyYear.Text);
                Migra.ExportDocuments.dontReloadXml = chbdontReloadXml.Checked;
                CountDocs = Migra.ExportDocuments.CountRows(connection);
                prbDocuments.Maximum = CountDocs;
                lblDocumentsAll.Text = prbDocuments.Maximum.ToString();
                Migra.ExportDocuments.Execute(txtFolder.Text, connection);
            }
            lblProgress.Text = "Состояние выгрузки: Выгрузка завершена!";
            MessageBox.Show("Выгрузка завершена!");
            btnExport.Enabled = true;
            if (localdb != null) localdb.Connection.Close();
            CountDocs = 0;
        }
        private void ProgressMessage(string message)
        {
            lblProgress.Text = message;
            prbDocuments.Maximum = Migra.ExportTables.CountTask;
            lblDocumentsAll.Text = prbDocuments.Maximum.ToString();
            prbDocuments.Update();
            lblProgress.Update();
            lblDocumentsAll.Update();
        }

        private DateTime TimeProgress = DateTime.Now;
        private Int32 CountCurrentDocLast = 0;
        private void Progress(int progress)
        {
            TimeSpan Tik = (DateTime.Now - TimeProgress);
            if (Tik.TotalSeconds < 2)
            {               
                return;
            }
            prbDocuments.Value = progress;
            lblDocumentProgress.Text = progress.ToString();
            TimeSpan lasttime = (DateTime.Now - StartExport);
            lblLastTime.Text = lasttime.ToString();
            if (progress > 2)
            {
                lblSpeedOne.Text = (((progress - CountCurrentDocLast) / Tik.TotalSeconds) ).ToString();
                lblLastTimeOne.Text = TimeSpan.FromMilliseconds((prbDocuments.Maximum - progress) / ((progress - CountCurrentDocLast) / Tik.TotalMilliseconds)).ToString();
                lblSpeedGeneral.Text = ((progress / lasttime.TotalSeconds)).ToString().Substring(0, 6);
                lblLastTimeGeneral.Text = TimeSpan.FromMilliseconds((prbDocuments.Maximum - progress) / (progress / lasttime.TotalMilliseconds)).ToString();
                Double alltime = lasttime.Ticks * prbDocuments.Maximum / progress;
                lblAllTime.Text = TimeSpan.FromTicks((long)alltime).ToString();
                
            }
            lblDocumentProgress.Update();
            prbDocuments.Update();
            lblAllTime.Update();
            lblLastTime.Update();
            lblLastTimeOne.Update();
            lblLastTimeGeneral.Update();
            lblSpeedGeneral.Update();
            lblSpeedOne.Update();
            TimeProgress = DateTime.Now;
            CountCurrentDocLast = progress;
        }

        private void btnApiConnect_Click(object sender, EventArgs e)
        {
            fApiConnect.ShowDialog(this);
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderLoad.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.analyz = Migra.TAnalys.byname;
            localdb.sqlprefix = SqlPrefix;
            localdb.topRcDocgroup = cmbtopRcDocgroup.Text.Split(new string[1] {"-|-"},StringSplitOptions.None)[1].Trim();
            localdb.defaultRcIn = cmbdefaultRcIn.Text.Split(new string[1] {"-|-"},StringSplitOptions.None)[1].Trim();
            localdb.defaultRcOut = cmbdefaultRcOut.Text.Split(new string[1] {"-|-"},StringSplitOptions.None)[1].Trim();
            localdb.defaultRcLet = cmbdefaultRcLet.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
            localdb.topDepartment = cmbtopDepartment.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
            localdb.topRubric = cmbtopRubric.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
            localdb.topOrganiz = cmbtopOrganiz.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim(); 
            localdb.SyncConnection = connection;
            localdb.topCard = cmbdefaultCard.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
            Migra.ImportDocuments.txtImportError = txtImportError;
            Migra.ImportDocuments.localdb = localdb;
            lblImportCount.Text = "0";
            Migra.ImportDocuments.CurrentRows += ImportProgress;
            StartExport = DateTime.Now;
            // Дополнительные реквизиты
            localdb.ARRCVALUE.Clear();
            for (int i = 0; i < listViewEx1.Items.Count; i++)
            {
                if ((listViewEx1.Items[i].SubItems.Count >= 2) && (listViewEx1.Items[i].SubItems[1].Text != ""))
                {
                    localdb.ARRCVALUE.Add(listViewEx1.Items[i].Text, listViewEx1.Items[i].SubItems[1].Text);
                }
            }
            Migra.ImportDocuments.Import12mss(txtFolderLoad.Text, connection, ApiConnection);
            MessageBox.Show("Загрузка документов завершена!");
        }

        private void ImportProgress(int progress)
        {
            TimeSpan Tik = (DateTime.Now - TimeProgress);
            if (Tik.TotalSeconds < 2)
            {
                return;
            }
            lblImportAll.Text = Migra.ImportDocuments.CountFiles.ToString();
            prbImportDocuments.Maximum = Migra.ImportDocuments.CountFiles;
            lblImportAll.Update();
            prbImportDocuments.Update();
            TimeProgress = DateTime.Now;
            prbImportDocuments.Value = progress;
            lblImportCount.Text = progress.ToString();
            prbImportDocuments.Update();
            lblImportCount.Update();
            TimeSpan lasttime = (DateTime.Now - StartExport);
            lblLastTimeImport.Text = lasttime.ToString();
            if (progress > 2)
            {
                Double alltime = lasttime.Ticks * prbImportDocuments.Maximum / progress;
                lblAllTimeImport.Text = TimeSpan.FromTicks((long)alltime).ToString();
            }
            lblAllTimeImport.Update();
            lblLastTimeImport.Update();
        }

        private void cmbtopRcDocgroup_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select CLASSIF_NAME, DUE, LAYER FROM " + SqlPrefix + "DOCGROUP_CL WHERE (IS_NODE=0) and (CLASSIF_NAME like '"+txtCodePrefix.Text+"%') ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l ; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }

        }

        private void cmbdefaultRc_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string where = "";
            if ((sender as ComboBox).Name.IndexOf("In") > 0) where = " WHERE ((RC_TYPE=1) OR (IS_NODE=0)) ";
            if ((sender as ComboBox).Name.IndexOf("Let") > 0) where = " WHERE ((RC_TYPE=2) OR (IS_NODE=0)) ";
            if ((sender as ComboBox).Name.IndexOf("Out") > 0) where = " WHERE ((RC_TYPE=3) OR (IS_NODE=0)) ";
            string sql = "select CLASSIF_NAME, DUE, LAYER, IS_NODE FROM " + SqlPrefix + "DOCGROUP_CL"+where+" and (CLASSIF_NAME like '"+txtCodePrefix.Text+"%') ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }

        }

        private void cmbtopRubric_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select CLASSIF_NAME, DUE, LAYER FROM " + SqlPrefix + "RUBRIC_CL WHERE (IS_NODE=0) and (CLASSIF_NAME like '" + txtCodePrefix.Text + "%') ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }

        }

        private void cmbtopDepartment_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select CLASSIF_NAME, DUE, LAYER FROM " + SqlPrefix + "DEPARTMENT WHERE (IS_NODE=0) and (CLASSIF_NAME like '" + txtCodePrefix.Text + "%') ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }
        }

        private void cmbtopOrganiz_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select CLASSIF_NAME, DUE, LAYER FROM " + SqlPrefix + "ORGANIZ_CL WHERE IS_NODE=0 ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }
        }

        private void trbThreadsCount_Scroll(object sender, EventArgs e)
        {
            lblThreadsCount.Text = trbThreadsCount.Value.ToString();
            Migra.ExportDocuments.MaxThread = trbThreadsCount.Value;
        }

        private void chbOnlyYear_CheckedChanged(object sender, EventArgs e)
        {
            if (!chbOnlyYear.Checked)
            {
                txtOnlyYear.Text = "";
                txtOnlyYear.Enabled = false;
            }
            else
            {
                txtOnlyYear.Enabled = true;
            }
        }

        private void chbExportTables_CheckedChanged(object sender, EventArgs e)
        {
            if (chbExportTables.Checked) cmbLocalDBOpen.Enabled = true;
            else cmbLocalDBOpen.Enabled = false;
        }

        private void chbExportDocuments_CheckedChanged(object sender, EventArgs e)
        {
            if (chbExportDocuments.Checked)
            {
                chbOnlyYear.Enabled = true;
                txtOnlyYear.Enabled = true;
            }
            else
            {
                chbOnlyYear.Enabled = false;
                txtOnlyYear.Enabled = false;
            }
        }

        private void cmbGuideSync_SelectedIndexChanged(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderLoad.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            OleDbConnection clocaldb = localdb.Connection;
            //OleDbConnection clocaldb = Migra.ExportTables.localdb.Connection;
            OleDbConnection cprotocoldb = connection;
            if ((clocaldb == null) || ((clocaldb.State & ConnectionState.Open) != ConnectionState.Open)) return;
            if ((cprotocoldb == null) || ((cprotocoldb.State & ConnectionState.Open) != ConnectionState.Open)) return;
            switch ((sender as ComboBox).SelectedIndex)
            {
                case 0:
                    LoadLSV(lsvProtocolSync, clocaldb, "DOCGROUP");
                    LoadLSV(lsvDBSync, cprotocoldb, SqlPrefix + "DOCGROUP_CL");
                    break;
                case 1:
                    LoadLSV(lsvProtocolSync, clocaldb, "DEPARTMENT");
                    LoadLSV(lsvDBSync, cprotocoldb, SqlPrefix + "DEPARTMENT");
                    break;
                case 2:
                    LoadLSV(lsvProtocolSync, clocaldb, "RUBRIC_CL");
                    LoadLSV(lsvDBSync, cprotocoldb, SqlPrefix + "RUBRIC_CL");
                    break;
                default:
                    break;
            }
            

        }

        private void LoadLSV(ListView lsv, OleDbConnection connection, String Table)
        {
            String sql;
            if (connection.Provider.IndexOf("Ora") >-1)
            sql = "SELECT ISN_NODE, DUE, CLASSIF_NAME FROM "+Table + " ORDER BY DUE";
            else sql = "SELECT ISN_NODE, DUE, CLASSIF_NAME, IMPORTISN, IMPORTDUE FROM " + Table + " ORDER BY DUE";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            lsv.Items.Clear();
            while (dr.Read())
            {
                ListViewItem Item = lsv.Items.Add(dr[0].ToString());
                String due = dr[1].ToString();
                Item.SubItems.Add(due);
                String name = dr[2].ToString();
                for (int i = 3; i < due.Split('.').Length; i++)
                {
                    name = "    " + name;
                }
                Item.SubItems.Add(name);
                if (connection.Provider.IndexOf("Ora") == -1)
                {
                    if (!dr.IsDBNull(3))
                        Item.SubItems.Add(dr[3].ToString());
                    if (!dr.IsDBNull(4))
                        Item.SubItems.Add(dr[4].ToString());
                }
            }
            dr.Close();
        }

        private void frmMain_SizeChanged(object sender, EventArgs e)
        {
            lsvProtocolSync.Width = (tdpSyncGuide.ClientSize.Width / 2) - 7;
            lsvDBSync.Width = lsvProtocolSync.Width;
            lsvDBSync.Left = lsvDBSync.Width + 14;
        }

        private void lsvDBSync_QueryContinueDrag(object sender, QueryContinueDragEventArgs e)
        {
            
        }

        private ListView containDrag;
        private List<ListViewItem> DragingLine = new List<ListViewItem>();

        private void lsvProtocolSync_DoubleClick(object sender, EventArgs e)
        {
            ListView lsv = (sender as ListView);
            lsv.Items.RemoveAt(lsv.SelectedItems[0].Index);
        }

        private void lsvProtocolSync_MouseUp(object sender, MouseEventArgs e)
        {
            ListView lsv = (sender as ListView);
            if ((lsv.SelectedItems.Count==1) && (e.Button == System.Windows.Forms.MouseButtons.Middle))
            {
                lsv.Items.Insert(lsv.SelectedItems[0].Index, "");
            } else 
            if (e.Button == System.Windows.Forms.MouseButtons.Right)
            {
                if ((containDrag == null) || (containDrag != (sender as ListView)))
                {
                    containDrag = (sender as ListView);
                    DragingLine.Clear();
                }
                if (DragingLine.Count == 0)
                {
                    DragingLine.AddRange(containDrag.SelectedItems.Cast<ListViewItem>());
                }
                else
                {
                    if (containDrag.SelectedItems.Count == 1)
                    {
                        while (DragingLine.Count > 0)
                        {
                            ListViewItem Item = containDrag.SelectedItems[0];
                            containDrag.Items.RemoveAt(DragingLine[0].Index);
                            containDrag.Items.Insert(Item.Index, DragingLine[0]);
                            DragingLine.RemoveAt(0);
                        }
                    }
                }
            }
        }

        private void btnSaveSync_Click(object sender, EventArgs e)
        {
            if (lsvDBSync.Items.Count != lsvProtocolSync.Items.Count) return;
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderLoad.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            for (int i = 0; i < lsvProtocolSync.Items.Count; i++)
            {
                ListViewItem Item = lsvProtocolSync.Items[i];
                switch(cmbGuideSync.SelectedIndex)
                {
                    case 0:
                        localdb.SaveDocgroupNewID(Int32.Parse(Item.SubItems[0].Text), Int32.Parse(Item.SubItems[3].Text), Item.SubItems[4].Text);
                        break;
                    case 1:
                        localdb.SaveDepartmentNewID(Int32.Parse(Item.SubItems[0].Text), Int32.Parse(Item.SubItems[3].Text), Item.SubItems[4].Text);
                        break;
                    case 2:
                        localdb.SaveRubricNewID(Int32.Parse(Item.SubItems[0].Text), Int32.Parse(Item.SubItems[3].Text), Item.SubItems[4].Text);
                        break;
                }
            }
        }

        private void btnClearNotSelected_Click(object sender, EventArgs e)
        {
            int i = 0;
            while (i < lsvDBSync.Items.Count)
            {
                if (!lsvDBSync.Items[i].Selected)
                {
                    lsvDBSync.Items[i].Remove();
                    continue;
                }
                i++;
            }
            i = 0;
            while (i < lsvProtocolSync.Items.Count)
            {
                if (!lsvProtocolSync.Items[i].Selected)
                {
                    lsvProtocolSync.Items[i].Remove();
                    continue;
                }
                i++;
            }
            if (lsvDBSync.Items.Count != lsvProtocolSync.Items.Count)
            {
                MessageBox.Show("Не одинаковое кол-во строк\n" + lsvProtocolSync.Items.Count.ToString() + "\\" + lsvDBSync.Items.Count.ToString());
                return;
            }
            MessageBox.Show("Строк=" +lsvDBSync.Items.Count.ToString());
        }

        private void btnPareSync_Click(object sender, EventArgs e)
        {
            if (lsvDBSync.Items.Count != lsvProtocolSync.Items.Count) return;
            for (int i = 0; i < lsvProtocolSync.Items.Count; i++)
            {
                ListViewItem Item = lsvProtocolSync.Items[i];
                if (Item.SubItems.Count<4) Item.SubItems.Add("");
                Item.SubItems[3].Text = lsvDBSync.Items[i].Text;
                if (Item.SubItems.Count<5) Item.SubItems.Add("");
                Item.SubItems[4].Text = lsvDBSync.Items[i].SubItems[1].Text;
                if (Item.SubItems.Count<6) Item.SubItems.Add("");
                Item.SubItems[5].Text = lsvDBSync.Items[i].SubItems[2].Text;
            }
        }

        private void btnStartVerif_Click(object sender, EventArgs e)
        {
            btnStartVerif.Enabled = false;
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            Migra.Check.lsvCheckError = lsvStatusVerif;
            Migra.Check.CurrentRows += CheckProgress;
            TimeProgress = DateTime.Now;
            StartExport = TimeProgress;
            Migra.Check.Import12mss(txtFolderVerif.Text, connection, ApiConnection);
            
            btnStartVerif.Enabled = true;
            MessageBox.Show("Сверка завершена!");
            CheckProgress(Migra.Check.CountFiles);
        }

        private void CheckProgress(int progress)
        {
            TimeSpan Tik = (DateTime.Now - TimeProgress);
            if (Tik.TotalSeconds < 2)
            {
                return;
            }
            lblVerifAll.Text = Migra.Check.CountFiles.ToString();
            prbCheckVerif.Maximum = Migra.Check.CountFiles;
            TimeProgress = DateTime.Now;
            prbCheckVerif.Value = progress;
            lblVerifCount.Text = progress.ToString();
            TimeSpan lasttime = (DateTime.Now - StartExport);
            lblLastTimeVerif.Text = lasttime.ToString();
            if (progress > 2)
            {
                Double alltime = lasttime.Ticks * prbCheckVerif.Maximum / progress;
                lblAllTimeCheck.Text = TimeSpan.FromTicks((long)alltime).ToString();
            }
            lblErrorVerif.Text = Migra.Check.ErrorFiles.ToString();
            prbCheckVerif.Update();
            lblVerifCount.Update();
            lblVerifAll.Update();
            lblAllTimeCheck.Update();
            lblLastTimeVerif.Update();
            lblErrorVerif.Update();
        }

        private void btnCorrectDVO_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            Migra.Check.CorrectДВО(connection);
            MessageBox.Show("Коррекция номеров ДВО завершена!");
        }

        private void btnStartVerifCheck_Click(object sender, EventArgs e)
        {
            btnStartVerifCheck.Enabled = false;
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            Migra.Check.lsvCheckError = lsvStatusVerif;
            Migra.Check.CurrentRows += CheckProgress;
            TimeProgress = DateTime.Now;
            StartExport = TimeProgress;
            Migra.Check.CheckFile(txtFolderVerif.Text, connection, ApiConnection);

            btnStartVerifCheck.Enabled = true;
            MessageBox.Show("Сверка завершена!");

        }

        private void загрузитьToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (lsvStatusVerif.SelectedItems.Count == 0) return;
            try
            {
                if ((cmbtopRcDocgroup.Text.IndexOf("-|-") == -1) || (cmbdefaultRcIn.Text.IndexOf("-|-") == -1) ||
                    (cmbdefaultRcOut.Text.IndexOf("-|-") == -1) || (cmbdefaultRcLet.Text.IndexOf("-|-") == -1) ||
                    (cmbtopDepartment.Text.IndexOf("-|-") == -1) || (cmbtopRubric.Text.IndexOf("-|-") == -1) || (cmbtopRubric.Text.IndexOf("-|-") == -1))
                {
                    tabPage4.Select();
                    MessageBox.Show("Не возможно выполнить загрузку так как не заданы значения по умолчанию!");
                    return;
                }
                Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
                localdb.sqlprefix = SqlPrefix;
                localdb.analyz = Migra.TAnalys.byname;
                Migra.ImportDocuments.localdb = localdb;
                ListViewItem item = lsvStatusVerif.SelectedItems[0];
                XmlDocument doc = new XmlDocument();
                doc.Load(item.Text);
                localdb.SyncConnection = connection;
                localdb.checksynctable = false;
                localdb.topRcDocgroup = cmbtopRcDocgroup.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.defaultRcIn = cmbdefaultRcIn.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.defaultRcOut = cmbdefaultRcOut.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.defaultRcLet = cmbdefaultRcLet.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.topDepartment = cmbtopDepartment.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.topRubric = cmbtopRubric.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                localdb.topOrganiz = cmbtopOrganiz.Text.Split(new string[1] { "-|-" }, StringSplitOptions.None)[1].Trim();
                Migra.ImportDocuments.checksave = false;
                Migra.ImportDocuments.SaveRC(doc, ApiConnection);
                localdb.Connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnCopyJournal_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.SyncConnection = connection;
            OleDbTransaction trans = localdb.Connection.BeginTransaction();
            String sql = "CREATE TABLE JOURNAL ( ";
            sql += "ISN_JOURNAL BIGINT PRIMARY KEY, ";
            sql += "KIND_DOC int, ";
            sql += "ISN_DOC BIGINT, ";
            sql += "DUE_ADRESAT NVARCHAR(64), ";
            sql += "SEND_DATE NVARCHAR(64), ";
            sql += "NOTES NVARCHAR(255), ";
            sql += "ORIGINAL_NUM INT, ORIG_FLAG INT, OWNER_FLAG INT) ";

            OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection,trans);
            cmd.ExecuteNonQuery();

            sql = "select ISN_JOURNAL,KIND_DOC,ISN_DOC,DUE_ADRESAT,SEND_DATE,NOTES,ORIGINAL_NUM,ORIG_FLAG,OWNER_FLAG FROM JOURNAL";
            cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            try
            {
                while (dr.Read())
                {
                    try
                    {
                        sql = "INSERT INTO JOURNAL VALUES (";
                        sql += dr.GetValue(0).ToString() + ",";
                        sql += dr.GetValue(1).ToString() + ",";
                        sql += dr.GetValue(2).ToString() + ",";
                        sql += "'" + dr.GetValue(3).ToString() + "',";
                        sql += "'" + dr.GetValue(4).ToString() + "',";
                        if (dr.IsDBNull(5)) sql += "null,";
                        else sql += "'" + dr.GetValue(5).ToString() + "',";
                        sql += dr.GetValue(6).ToString() + ",";
                        sql += dr.GetValue(7).ToString() + ",";
                        sql += dr.GetValue(8).ToString() + ")";
                        cmd = new OleDbCommand(sql, localdb.Connection, trans);
                        cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        continue;
                    }
                }
                trans.Commit();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message +"\n"+sql );
                trans.Rollback();
            }
        }

        private void btnJournalCopyDB_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.SyncConnection = connection;
            OleDbTransaction trans = connection.BeginTransaction();
            String sql = "";
            Int32 progress = 0; 
            try
            {
                sql = "SELECT J.KIND_DOC, P.IMPORTISN, D.IMPORTDUE, J.SEND_DATE, J.NOTES, J.ORIGINAL_NUM, J.ORIG_FLAG, J.OWNER_FLAG ";
                sql += " FROM JOURNAL AS J INNER JOIN PROTOCOL AS P ON J.ISN_DOC = P.EXPORTISN INNER JOIN ";
                sql += " DEPARTMENT AS D ON J.DUE_ADRESAT = D.DUE WHERE (P.TNAME = N'RC')";
                OleDbCommand ExecCmd = new OleDbCommand(sql, localdb.Connection);
                OleDbDataReader dr = ExecCmd.ExecuteReader();
                long dt = DateTime.Now.Ticks;
                int sec =0, oldsec=1000;
                
                while (dr.Read())
                {
                    try
                    {
                        ExecCmd = new OleDbCommand();
                        ExecCmd.Connection = localdb.SyncConnection;
                        ExecCmd.CommandType = CommandType.StoredProcedure;
                        ExecCmd.CommandText = "GET_SEQ";
                        ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
                        ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
                        ExecCmd.Parameters.Add("airet", OleDbType.Integer);
                        ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
                        ExecCmd.Transaction = trans;
                        ExecCmd.ExecuteNonQuery();
                        Int32 isn = (Int32)(ExecCmd.Parameters["airet"].Value);
                        ExecCmd.Dispose();
                        sql = "insert into " + SqlPrefix + "journal (isn_journal, kind_doc, isn_doc, due_corresp, isn_cabinet, isn_nomenc,";
                        sql += "due_adresat, send_date, reestr_date, reestr_num, notes, original_num,copy_name, orig_flag, owner_flag, rec_type,";
                        sql += "vol_num, vol_str, str_doc, ins_date, ins_who, upd_date, upd_who) values (" + isn.ToString() + "," + dr.GetValue(0).ToString();
                        sql += "," + dr.GetValue(1).ToString() + ",'0.',0,0,'" + dr.GetValue(2).ToString() + "',TO_DATE('" + dr.GetValue(3).ToString().Split('.')[0] + "','YYYY-MM-DD HH24:MI:SS'),null,0,";
                        if (dr.IsDBNull(4)) sql += "null,";
                        else sql += "'" + dr.GetValue(4).ToString() + "',";
                        sql += dr.GetValue(5).ToString() + ",null," + dr.GetValue(6).ToString() + "," + dr.GetValue(7).ToString();
                        sql += ",2,null, null, null, null, null, null, null)";
                        ExecCmd = new OleDbCommand(sql, connection, trans);
                        ExecCmd.ExecuteNonQuery();
                        ExecCmd.Dispose();
                        progress++;
                        sec = TimeSpan.FromTicks(DateTime.Now.Ticks - dt).Seconds;
                        if (sec != oldsec)
                        {
                            lblVerifAll.Text = progress.ToString();
                            lblVerifAll.Update();
                            lblLastTimeVerif.Text = TimeSpan.FromTicks(DateTime.Now.Ticks - dt).ToString();
                            lblLastTimeVerif.Update();
                            oldsec =sec;
                        }
                        //System.Windows.Forms.Application.DoEvents();
                    }
                    catch (Exception ex)
                    {
                        System.IO.File.AppendAllText(txtFolderVerif.Text + "\\journal.log", sql + "\r\n" + ex.Message);
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + sql);
                trans.Rollback();
                return;
            }
            MessageBox.Show("Журнал успешно загружен");
        }

        private void cmbdefaultCard_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select CARD_NAME, DUE, LAYER FROM " + SqlPrefix + "DEPARTMENT WHERE (CARD_FLAG=1) and (CLASSIF_NAME like '" + txtCodePrefix.Text + "%') ORDER BY DUE ";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            string space;
            while (dr.Read())
            {
                space = "";
                Int16 l = (Int16)0;
                if (!dr.IsDBNull(2)) l = Int16.Parse(dr.GetValue(2).ToString());
                for (Int16 i = (Int16)0; i < l; i++)
                {
                    space += "    ";
                }
                (sender as ComboBox).Items.Add(space + dr.GetString(0) + "-|-" + dr.GetString(1));
            }
        }

        
        private void btnDepartmentTree_Click(object sender, EventArgs e)
        {
            if ((connection == null) || (connection.State == ConnectionState.Closed))
            {
                MessageBox.Show("Отсутсвует подключение");
                return;
            }
            trvDepartment.Nodes.Clear();
            string sql = "select count(*) from " + SqlPrefix + "DEPARTMENT";
            OleDbCommand cmd_count = new OleDbCommand(sql, connection);
            prbDepartmentJoin.Maximum=int.Parse(cmd_count.ExecuteScalar().ToString());
            prbDepartmentJoin.Value = 0;
            cmd_count.Dispose();
            trvDepartment.BeginUpdate();
            sql = "select CLASSIF_NAME, CARD_NAME, CARD_FLAG, ISN_NODE, IS_NODE, DELETED FROM " +
                SqlPrefix+"DEPARTMENT WHERE (ISN_HIGH_NODE is NULL)";
            if ((cmbSortName.SelectedIndex == 1)&&(chb_withDeleted.Checked==true))
            {
                sql += " ORDER BY CLASSIF_NAME";
            }
            else if ((cmbSortName.SelectedIndex == 0)&&(chb_withDeleted.Checked==false))
            {
                sql += " and (DELETED = 0) ORDER BY WEIGHT";
            }
            else
            {
                cmbSortName.SelectedIndex = 0;
                sql += " ORDER BY WEIGHT";
            }
            string caption = "";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                prbDepartmentJoin.Value += 1;
                caption = "";
                if (!dr.IsDBNull(0)) caption = dr.GetString(0);
                if (!dr.IsDBNull(1)) 
                    if (dr.GetString(1) != "") caption += "["+dr.GetString(1)+"]";
                    else caption += "[]";
                TreeNode tn = trvDepartment.Nodes.Add(caption);
                tn.Tag = dr.GetValue(3).ToString();
                if (!dr.IsDBNull(4))
                {
                    switch (dr.GetValue(4).ToString())
                    {
                        case "0":
                            tn.ImageIndex = 0;
                            tn.SelectedImageIndex = 0;
                            break;
                        case "1":
                            tn.ImageIndex = 2;
                            tn.SelectedImageIndex = 2;
                            break;
                    }
                    if (!dr.IsDBNull(5) && (dr.GetValue(5).ToString()=="1"))
                    {
                        tn.ImageIndex += 3;
                        tn.SelectedImageIndex += 3;
                    }
                }
                if (!dr.IsDBNull(3))
                    SubTreeDepartment(tn, int.Parse(dr.GetValue(3).ToString()));
            }
            dr.Close();
            cmd.Dispose();
            trvDepartment.EndUpdate();
            MessageBox.Show("Подразделения обновлены!");
        }

        private void SubTreeDepartment(TreeNode root, int parent)
        {
            string sql = "select CLASSIF_NAME, CARD_NAME, CARD_FLAG, ISN_NODE, IS_NODE, DELETED FROM " +
                SqlPrefix + "DEPARTMENT WHERE (ISN_HIGH_NODE = "+parent.ToString()+")";
            if ((cmbSortName.SelectedIndex == 1) && (chb_withDeleted.Checked == true))
            {
                sql += " ORDER BY CLASSIF_NAME";
            }
            else if ((cmbSortName.SelectedIndex == 0) && (chb_withDeleted.Checked == false))
            {
                sql += " and (DELETED = 0) ORDER BY WEIGHT";
            }
            else
            {
                cmbSortName.SelectedIndex = 0;
                sql += " ORDER BY WEIGHT";
            }

            string caption = "";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                prbDepartmentJoin.Value += 1;
                caption = "";
                if (!dr.IsDBNull(0)) caption = dr.GetString(0);
                if (!dr.IsDBNull(1))
                    if (dr.GetString(1) != "") caption += "[" + dr.GetString(1) + "]";
                    else caption += "[]";
                TreeNode tn = root.Nodes.Add(caption);
                tn.Tag = dr.GetValue(3).ToString();

                if (!dr.IsDBNull(4))
                {
                    switch (dr.GetValue(4).ToString())
                    {
                        case "0":
                            tn.ImageIndex = 0;
                            tn.SelectedImageIndex = 0;
                            break;
                        case "1":
                            tn.ImageIndex = 2;
                            tn.SelectedImageIndex = 2;
                            break;
                    }
                    if (!dr.IsDBNull(5) && (dr.GetValue(5).ToString() == "1"))
                    {
                        tn.ImageIndex += 3;
                        tn.SelectedImageIndex += 3;
                    }
                }
                if (!dr.IsDBNull(3))
                    SubTreeDepartment (tn, int.Parse(dr.GetValue(3).ToString()));
            }
            dr.Close();
            cmd.Dispose();

        }

        private void trvDepartment_AfterExpand(object sender, TreeViewEventArgs e)
        {
            e.Node.ImageIndex += 1;
            e.Node.SelectedImageIndex += 1;
        }

        private void trvDepartment_AfterCollapse(object sender, TreeViewEventArgs e)
        {
            e.Node.ImageIndex -= 1;
            e.Node.SelectedImageIndex -= 1;
        }

        private void trvDepartment_AfterSelect(object sender, TreeViewEventArgs e)
        {
            string sql = "select IS_NODE from " + SqlPrefix + "DEPARTMENT WHERE ISN_NODE = " + e.Node.Tag.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            object isnode = cmd.ExecuteScalar();
            cmd.Dispose();
            if ((isnode == null) || (isnode == DBNull.Value))
            {
                e.Node.Remove();
            }
            else
            {
                chbIsNodeDepartment.Checked = (isnode.ToString() == "1") ? true : false;
                chbIsNodeDepartment.Tag = e.Node;
            }
        }

        private void chbIsNodeDepartment_Click(object sender, EventArgs e)
        {
            if (chbIsNodeDepartment.Tag == null) return;
            bool deleted = ((chbIsNodeDepartment.Tag as TreeNode).ImageIndex > 2) ? true : false;
            string sql = "update " + SqlPrefix + "DEPARTMENT SET IS_NODE=" + (chbIsNodeDepartment.Checked ? "1" : "0") + " WHERE ISN_NODE = " + (chbIsNodeDepartment.Tag as TreeNode).Tag.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            cmd.ExecuteNonQuery();
            if (chbIsNodeDepartment.Checked)
            {
                (chbIsNodeDepartment.Tag as TreeNode).ImageIndex = 2;
                (chbIsNodeDepartment.Tag as TreeNode).SelectedImageIndex = 2;
            }
            else if ((chbIsNodeDepartment.Tag as TreeNode).IsExpanded)
            {
                (chbIsNodeDepartment.Tag as TreeNode).ImageIndex = 1;
                (chbIsNodeDepartment.Tag as TreeNode).SelectedImageIndex = 1;
            }
            else
            {
                (chbIsNodeDepartment.Tag as TreeNode).ImageIndex = 0;
                (chbIsNodeDepartment.Tag as TreeNode).SelectedImageIndex = 0;
            }
            if (deleted)
            {
                (chbIsNodeDepartment.Tag as TreeNode).ImageIndex += 3;
                (chbIsNodeDepartment.Tag as TreeNode).SelectedImageIndex += 3;
            }
        }

        private List<int> listDepartmentJoin = new List<int>();
        private void trvDepartment_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (trvDepartment.SelectedNode == null) return;
                if (!listDepartmentJoin.Contains(int.Parse(trvDepartment.SelectedNode.Tag.ToString())))
                {
                    listDepartmentJoin.Add(int.Parse(trvDepartment.SelectedNode.Tag.ToString()));
                    lsbDepartmentJoin.Items.Add(trvDepartment.SelectedNode.FullPath);
                }
            }
            else if (e.Button == MouseButtons.Left)
            {
                TreeNode trn = trvDepartment.GetNodeAt(e.Location);
                if (trn != null)
                {
                    trvDepartment.DoDragDrop(trn, DragDropEffects.Copy);
                    trvDepartment.SelectedNode = trn;
                }
            }
            else if (e.Button == MouseButtons.Middle)
            {
                if (trvDepartment.SelectedNode == null) return;
                string sql = "select DUE FROM " + SqlPrefix + "DEPARTMENT WHERE (ISN_NODE=" + trvDepartment.SelectedNode.Tag.ToString() + ") ";
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                object due = cmd.ExecuteScalar();
                cmd.Dispose();
                if (due == null || due == DBNull.Value) return;
                lsbCardViewer.Items.Clear();
                if (Migra.Check.localdb == null)
                {
                    Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtDepartmentJoinPath.Text + "\\protocol.sdf", Migra.TModeLocalDB.Create);
                    localdb.sqlprefix = SqlPrefix;
                    Migra.Check.localdb = localdb;
                }
                prbDepartmentJoin.Maximum = Migra.Check.CountFiles;
                Migra.Check.CurrentRows += prbDepartmentJoinProgress;
                lsbCardViewer.Items.AddRange(Migra.Check.ViewCardFromDepartment(connection, due.ToString()).ToArray());
            }
        }

        private void prbDepartmentJoinProgress(int progress)
        {
            prbDepartmentJoin.Maximum = Migra.Check.CountFiles;
            prbDepartmentJoin.Value = progress;
        }

        private void lsbDepartmentJoin_MouseDown(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (lsbDepartmentJoin.SelectedIndex == -1) return;
                listDepartmentJoin.RemoveAt(lsbDepartmentJoin.SelectedIndex);
                lsbDepartmentJoin.Items.RemoveAt(lsbDepartmentJoin.SelectedIndex);
            }
            else if (e.Button == MouseButtons.Left)
            {
                lsbDepartmentJoin.DoDragDrop(lsbDepartmentJoin.SelectedIndex, DragDropEffects.Copy);
            }
        }

        private void btnJoin_Click(object sender, EventArgs e)
        {
            if ((lsbDepartmentJoin.Items.Count < 2) || (connection==null) || (connection.State == ConnectionState.Closed)) return;
            List<string> listDueDepartmentJoin = new List<string>();
            OleDbCommand ExecCmd = new OleDbCommand();
            ExecCmd.Connection = connection;
            ExecCmd.CommandType = CommandType.Text;
            ExecCmd.CommandText = "SELECT DUE FROM "+SqlPrefix+"DEPARTMENT WHERE ISN_NODE=?";
            ExecCmd.Prepare();
            for (int i = 0; i < listDepartmentJoin.Count; i++)
            {
                ExecCmd.Parameters.Clear();
                ExecCmd.Parameters.Add("@p1", OleDbType.Integer);
                ExecCmd.Parameters[0].Value = listDepartmentJoin[i];
                listDueDepartmentJoin.Add(ExecCmd.ExecuteScalar().ToString());               
            }
            if (Migra.Check.localdb == null)
            {
                Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtDepartmentJoinPath.Text + "\\protocol.sdf", Migra.TModeLocalDB.Create);
                localdb.sqlprefix = SqlPrefix;
                Migra.Check.localdb = localdb;
            }
            try
            {
                Migra.Check.JoinDepartments(connection, listDepartmentJoin[0], listDueDepartmentJoin[0], listDepartmentJoin, listDueDepartmentJoin);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            listDepartmentJoin.Clear();
            lsbDepartmentJoin.Items.Clear();
            MessageBox.Show("Выполнено!");
        }

        private void btnISNFactory_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            TimeProgress = DateTime.Now;
            StartExport = TimeProgress;
            Migra.Check.CurrentRows += CheckProgress;
            Migra.Check.IsnFactory(connection, txtFolderVerif.Text);
            MessageBox.Show("Коррекция  ISN-ов завершена!");
        }

        private void btnJournal20122013_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.SyncConnection = connection;
            OleDbTransaction trans = connection.BeginTransaction();
            OleDbCommand ExecCmd;
            OleDbDataReader dr;
            String sql = "";
            Int32 progress = 0;
            try
            {
                sql = "SELECT J.KIND_DOC, P.IMPORTISN, D.IMPORTDUE, J.SEND_DATE, J.NOTES, J.ORIGINAL_NUM, J.ORIG_FLAG, J.OWNER_FLAG ";
                sql += " FROM JOURNAL AS J INNER JOIN PROTOCOL AS P ON J.ISN_DOC = P.EXPORTISN INNER JOIN ";
                sql += " DEPARTMENT AS D ON J.DUE_ADRESAT = D.DUE WHERE (P.TNAME = N'RC') AND ((P.IMPORTDUE = '2012') OR (P.IMPORTDUE = '2013'))";
                ExecCmd = new OleDbCommand(sql, localdb.Connection);
                dr = ExecCmd.ExecuteReader();
                long dt = DateTime.Now.Ticks;
                int sec = 0, oldsec = 1000;

                while (dr.Read())
                {
                    //prbCheckVerif.Value = progress;
                    try
                    {
                        ExecCmd = new OleDbCommand();
                        ExecCmd.Connection = localdb.SyncConnection;
                        ExecCmd.CommandType = CommandType.StoredProcedure;
                        ExecCmd.CommandText = "GET_SEQ";
                        ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
                        ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
                        ExecCmd.Parameters.Add("airet", OleDbType.Integer);
                        ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
                        ExecCmd.Transaction = trans;
                        ExecCmd.ExecuteNonQuery();
                        Int32 isn = (Int32)(ExecCmd.Parameters["airet"].Value);
                        ExecCmd.Dispose();
                        sql = "insert into " + SqlPrefix + "journal (isn_journal, kind_doc, isn_doc, due_corresp, isn_cabinet, isn_nomenc,";
                        sql += "due_adresat, send_date, reestr_date, reestr_num, notes, original_num,copy_name, orig_flag, owner_flag, rec_type,";
                        sql += "vol_num, vol_str, str_doc, ins_date, ins_who, upd_date, upd_who) values (" + isn.ToString() + "," + dr.GetValue(0).ToString();
                        sql += "," + dr.GetValue(1).ToString() + ",'0.',0,0,'" + dr.GetValue(2).ToString() + "',TO_DATE('" + dr.GetValue(3).ToString().Split('.')[0] + "','YYYY-MM-DD HH24:MI:SS'),null,0,";
                        if (dr.IsDBNull(4)) sql += "null,";
                        else sql += "'" + dr.GetValue(4).ToString() + "',";
                        sql += dr.GetValue(5).ToString() + ",null," + dr.GetValue(6).ToString() + "," + dr.GetValue(7).ToString();
                        sql += ",2,null, null, null, null, null, null, null)";
                        ExecCmd = new OleDbCommand(sql, connection, trans);
                        ExecCmd.ExecuteNonQuery();
                        ExecCmd.Dispose();
                        progress++;
                        sec = TimeSpan.FromTicks(DateTime.Now.Ticks - dt).Seconds;
                        if (sec != oldsec)
                        {
                            lblVerifAll.Text = progress.ToString();
                            lblVerifAll.Update();
                            lblLastTimeVerif.Text = TimeSpan.FromTicks(DateTime.Now.Ticks - dt).ToString();
                            lblLastTimeVerif.Update();
                            oldsec = sec;
                        }
                        //System.Windows.Forms.Application.DoEvents();
                    }
                    catch (Exception ex)
                    {
                        System.IO.File.AppendAllText(txtFolderVerif.Text + "\\journal.log", sql + "\r\n" + ex.Message);
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\n" + sql);
                trans.Rollback();
                return;
            }
            MessageBox.Show("Журнал успешно загружен");

        }

        private void btnLinkLoad_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            localdb.SyncConnection = connection;
            TimeProgress = DateTime.Now;
            StartExport = TimeProgress;
            Migra.Check.CurrentRows += CheckProgress;
            prbCheckVerif.Value = 0; 
            Migra.Check.LinksFactory(connection, txtFolderVerif.Text,ApiConnection);
            MessageBox.Show("Загрузка связок завершена");
        }
        private void btnCoverDocLoad_Click(object sender, EventArgs e)
        {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            localdb.sqlprefix = SqlPrefix;
            Migra.Check.localdb = localdb;
            localdb.SyncConnection = connection;
            TimeProgress = DateTime.Now;
            StartExport = TimeProgress;
            Migra.Check.CurrentRows += CheckProgress;
            prbCheckVerif.Value = 0;
            Migra.Check.CoverDocsFactory(connection, txtFolderVerif.Text, ApiConnection);
            MessageBox.Show("Загрузка сопроводительных документов завершена");
        }


        private void listViewEx1_SubItemClicked(object sender, ListViewEx.SubItemEventArgs e)
        {
            if ((e.Item != null) && (e.SubItem == 1))
            {
                if (e.Item.SubItems.Count == 1) e.Item.SubItems.Add("");
                listViewEx1.StartEditing(cmbArValuesEdit, e.Item, e.SubItem);
            }
            
        }

        private void cmbArValuesEdit_DropDown(object sender, EventArgs e)
        {
            (sender as ComboBox).Items.Clear();
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            string sql = "select API_NAME,UI_NAME, AR_TYPE from " + SqlPrefix + "AR_DESCRIPT";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                (sender as ComboBox).Items.Add(dr.GetString(0));               
            }
            dr.Close();
        }

        private void btnArValuesLoad_Click(object sender, EventArgs e)
        {
            listViewEx1.Items.Clear();
            if (connection == null) return;
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderLoad.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            string sql = "select EXPORTDUE FROM PROTOCOL WHERE TNAME='AR'";
            OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListViewItem item = listViewEx1.Items.Add(dr.GetString(0));
                sql = "select UI_NAME, AR_TYPE from " + SqlPrefix + "AR_DESCRIPT where API_NAME='" + dr.GetString(0) + "'";
                OleDbCommand cmd2 = new OleDbCommand(sql, connection);
                OleDbDataReader dr2 = cmd2.ExecuteReader();

                if (dr2.Read())
                {
                    item.SubItems.Add(dr.GetString(0));
                    item.SubItems.Add(dr2.GetString(0));
                    item.SubItems.Add(dr2.GetString(1));
                }
                dr2.Close();
            }
            dr.Close();

        }
        private void btnClearProtocol_Click(object sender, EventArgs e)
        {
            try
            {
            Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderLoad.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
            string sql = "delete PROTOCOL WHERE TNAME <> 'AR'";
            OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
            int res = cmd.ExecuteNonQuery();
            MessageBox.Show("Выполнение завершено. Очищено: " + res.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("ClearProtocol || " + ex.Message);
            }


        }
        private void btnClearDocCheck_Click(object sender, EventArgs e)
        {
            try
            {
                Migra.LocalDBCompact localdb = new Migra.LocalDBCompact(txtFolderVerif.Text + "\\protocol.sdf", Migra.TModeLocalDB.Exist);
                string sql = "delete PROTOCOL WHERE TNAME = 'DOC-CHECK'";
                OleDbCommand cmd = new OleDbCommand(sql, localdb.Connection);
                int res = cmd.ExecuteNonQuery();
                MessageBox.Show("Выполнение завершено. Очищено: " + res.ToString());
            }
            catch (Exception ex)
            {

                throw new Exception("ClearDocCheck || " + ex.Message);
            }


        }

        private void btnExportFile_Click(object sender, EventArgs e)
        {
            StartExport = DateTime.Now;
            btnExportFile.Enabled = false;
            Migra.ExportTables.CurrentRows += Progress;
            Migra.ExportTables.CurrentMessage += ProgressMessage;
            prbDocuments.Maximum = Migra.ExportTables.CountTask;
            prbDocuments.Value = 0;
            Migra.ExportTables.ExportFile(connection, txtFolder.Text);
            btnExportFile.Enabled = true;
            MessageBox.Show("Выгрузка файлов завершена!");
        }

        private void lsbDepartmentJoin_DragDrop(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(typeof(TreeNode)))
            {
                TreeNode trn = (TreeNode)e.Data.GetData(typeof(TreeNode));
                if (!listDepartmentJoin.Contains(int.Parse(trn.Tag.ToString())))
                {
                    listDepartmentJoin.Add(int.Parse(trn.Tag.ToString()));
                    lsbDepartmentJoin.Items.Add(trn.FullPath);
                }
            }
        }

        private void lsbDepartmentJoin_DragOver(object sender, DragEventArgs e)
        {
            e.Effect = DragDropEffects.Copy;
        }

        private void trvDepartment_DragDrop(object sender, DragEventArgs e)
        {
            if (e.Data.GetDataPresent(typeof(int)))
            {
                int SelectedIndex = (int)e.Data.GetData(typeof(int));
                if (SelectedIndex != -1)
                {
                    listDepartmentJoin.RemoveAt(SelectedIndex);
                    lsbDepartmentJoin.Items.RemoveAt(SelectedIndex);
                }
            }
        }

        private void trvDepartment_DoubleClick(object sender, EventArgs e)
        {
        }

        private void lsvAliasOrganiz_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void lsvAliasOrganiz_MouseDown(object sender, MouseEventArgs e)
        {

        }

        private void btnLoadFolderOrganization_Click(object sender, EventArgs e)
        {
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            lsvFolderOrganization.Items.Clear();
            string sql = "select CLASSIF_NAME, DUE, ISN_NODE from "+SqlPrefix+"ORGANIZ_CL WHERE (IS_NODE = 0) AND (DELETED = 0) AND (isn_high_node=0) ORDER BY WEIGHT";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListViewItem item = lsvFolderOrganization.Items.Add(dr.GetString(0));
                item.SubItems.Add(dr.GetString(1));
                item.SubItems.Add(dr.GetValue(2).ToString());
            }
            dr.Close();
        }

        private struct TAliasOrganiz
        {
            public string word;
            public string alias;
        }

        private List<TAliasOrganiz> AliasOrganizationDefault = new List<TAliasOrganiz>();
        private void btnCalculateOrganization_Click(object sender, EventArgs e)
        {
            ListViewItem lvItem;
            if (AliasOrganizationDefault.Count > 0)
            {
                lsvAliasOrganiz.Items.Clear();
                for (int i = 0; i < AliasOrganizationDefault.Count; i++)
                {
                    lvItem = lsvAliasOrganiz.Items.Add(AliasOrganizationDefault[i].word);
                    lvItem.SubItems.Add(AliasOrganizationDefault[i].alias);
                }
                AliasOrganizationDefault.Clear();
                btnCalculateOrganization.Text = "Отобразить двойников";
                lsvAliasOrganiz.ContextMenuStrip.Enabled = true;
            }
            else
            {
                if ((connection == null) || (connection.State != ConnectionState.Open)) return;
                string sql = "select CN, COUNT(*) as CNT from (select {0} as CN from " + SqlPrefix + "ORGANIZ_CL WHERE IS_NODE=1) GROUP BY CN HAVING COUNT(*) > 1 ORDER BY CNT DESC, CN ASC";
                string aliases = "REPLACE(UPPER(CLASSIF_NAME),CHR(39),'')";
                for (int i = 0; i < lsvAliasOrganiz.Items.Count; i++)
                {
                    TAliasOrganiz item = new TAliasOrganiz();
                    item.word = lsvAliasOrganiz.Items[i].SubItems[0].Text;
                    if (lsvAliasOrganiz.Items[i].SubItems.Count > 1)
                        item.alias = lsvAliasOrganiz.Items[i].SubItems[1].Text;
                    else item.alias = "";
                    
                    AliasOrganizationDefault.Add(item);
                    aliases = "REPLACE(" + aliases + ",'" + item.word.ToUpper().Replace("'", "") + "','" + item.alias.ToUpper().Replace("'", "") + "')";
                }
                lsvAliasOrganiz.Items.Clear();
                sql = string.Format(sql, aliases);
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                OleDbDataReader dr = cmd.ExecuteReader();
                Int32 CntAliases = 0;
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        lvItem=lsvAliasOrganiz.Items.Add(dr.GetString(0));
                        if (!dr.IsDBNull(1)) lvItem.SubItems.Add(dr.GetValue(1).ToString());
                        CntAliases++;
                    }
                }
                dr.Close();
                btnCalculateOrganization.Text = "Отобразить псевдонимы";
                lsvAliasOrganiz.ContextMenuStrip.Enabled = false;
                MessageBox.Show("Найдено организаций: "+ CntAliases.ToString());
            }
        }

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            ListViewItem Item = lsvAliasOrganiz.Items.Add("");
            Item.SubItems.Add("");
            lsvAliasOrganiz.FocusedItem = Item;            
        }

        private void toolStripMenuItem3_Click(object sender, EventArgs e)
        {
            if (lsvAliasOrganiz.SelectedItems.Count == 0) return;
            for (int i = 0; i < lsvAliasOrganiz.SelectedItems.Count; i++)
            {
                lsvAliasOrganiz.SelectedItems[i].Remove();
            }
        }


        private void btnJoinOrganization_Click(object sender, EventArgs e)
        {
            string Error = "";
            string Answer = "";
            string LogDeleted = "";
            string FizDeleted = "";
            if ((connection == null) || (connection.State != ConnectionState.Open))
            {
                MessageBox.Show("Отсутсвует подключение!");
                return;
            }
            if (lsvFolderOrganization.CheckedItems.Count == 0)
            {
               if( MessageBox.Show("Не выбраны фиксированые папки! Все равно продолжить?","Объединение организаций",MessageBoxButtons.YesNo) == DialogResult.No) return;
            }
            string sql1 = "select CN from (select {0} as CN from " + SqlPrefix + "ORGANIZ_CL WHERE IS_NODE=1) GROUP BY CN HAVING COUNT(*) > 1";
            string aliases = "REPLACE(UPPER(CLASSIF_NAME),CHR(39),'')";
            if (AliasOrganizationDefault.Count > 0)
            {
                for (int i = 0; i < AliasOrganizationDefault.Count; i++)
                {
                    TAliasOrganiz item = AliasOrganizationDefault[i];
                    aliases = "REPLACE(" + aliases + ",'" + item.word.ToUpper().Replace("'", "") + "','" + item.alias.ToUpper().Replace("'", "") + "')";
                }
            }
            else
            {
                for (int i = 0; i < lsvAliasOrganiz.Items.Count; i++)
                {
                    TAliasOrganiz item = new TAliasOrganiz();
                    item.word = lsvAliasOrganiz.Items[i].Text;
                    if (lsvAliasOrganiz.Items[i].SubItems.Count == 2)
                    {
                        item.alias = lsvAliasOrganiz.Items[i].SubItems[1].Text;
                    }
                    else
                    {
                        item.alias = "";
                    }
                    aliases = "REPLACE(" + aliases + ",'" + item.word.Replace("'", "") + "','" + item.alias.Replace("'", "") + "')";
                }
            }
            sql1 = string.Format(sql1, aliases);
            OleDbCommand cmd = new OleDbCommand("select count(*) from ("+sql1+")", connection);
            object cnt_res = cmd.ExecuteScalar();
            if (cnt_res == null || cnt_res == DBNull.Value) return;
            lblJoinOrganization.Text = cnt_res.ToString();
            prbJoinOrganization.Maximum = Int32.Parse(cnt_res.ToString());
            prbJoinOrganization.Value = 0;
            OleDbTransaction trans = connection.BeginTransaction();
            OleDbCommand cmd1 = new OleDbCommand(sql1, connection, trans);
            OleDbDataReader dr1 = cmd1.ExecuteReader();

            while (dr1.Read()) // Для каждой записи ищем двойников
            {
                if (dr1.IsDBNull(0)) continue;
                string sql2 = "select ISN_NODE, DUE FROM ORGANIZ_CL WHERE (IS_NODE=1) and (" + aliases + "='" + dr1.GetString(0) + "')";
                OleDbCommand cmd2 = new OleDbCommand(sql2, connection, trans);
                OleDbDataReader dr2 = cmd2.ExecuteReader();
                Int32 new_isn=0;
                string new_due="";
                List<Int32> old_isn = new List<int>();
                List<string> old_due = new List<string>();
                while (dr2.Read())
                {
                    if (dr2.IsDBNull(0) || dr2.IsDBNull(1)) continue;
                    string due = dr2.GetString(1);
                    foreach (ListViewItem folder_item in lsvFolderOrganization.CheckedItems)
                    {
                        if (folder_item.SubItems[1].Text.StartsWith(due))
                        {
                            if (new_due == "")
                            {
                                new_due = due;
                                new_isn = Int32.Parse(dr2.GetValue(0).ToString());
                            }
                            else continue; // если есть еще значения из папок то просто пропускаем их
                        }
                        else
                        {
                            if (!old_due.Contains(due)) old_due.Add(due);
                            if (!old_isn.Contains(Int32.Parse(dr2.GetValue(0).ToString()))) old_isn.Add(Int32.Parse(dr2.GetValue(0).ToString()));
                        }
                    }
                }
                dr2.Close();
                if (old_due.Count < 2) continue;
                if (new_due == "")
                {
                    new_due = old_due[0];
                    old_due.RemoveAt(0);
                    new_isn = old_isn[0];
                    old_isn.RemoveAt(0);
                }
                // Выполняем объединение
                for (int i = 0; i < old_due.Count; i++)
                {
                    if (new_due == old_due[i]) continue;
                    #region Инициализация процедурі вставки
                    OleDbCommand cmd3 = new OleDbCommand();
                    cmd3.Connection = connection;
                    cmd3.CommandType = CommandType.StoredProcedure;
                    cmd3.CommandText = "Join_Classif";
                    cmd3.Parameters.Add("aTable", OleDbType.VarChar);
                    cmd3.Parameters.Add("aDirect", OleDbType.VarChar);
                    cmd3.Parameters.Add("as_New_Due", OleDbType.VarChar);
                    cmd3.Parameters.Add("as_Due", OleDbType.VarChar);
                    cmd3.Parameters.Add("as_New_Isn_Node", OleDbType.VarChar);
                    cmd3.Parameters.Add("as_Isn_Node", OleDbType.VarChar);
                    cmd3.Parameters.Add("lAnswer", OleDbType.VarChar);
                    cmd3.Parameters.Add("aText", OleDbType.VarChar);
                    cmd3.Transaction = trans;
                    cmd3.Parameters["aTable"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["aDirect"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["as_New_Due"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["as_Due"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["as_New_Isn_Node"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["as_Isn_Node"].Direction = ParameterDirection.Input;
                    cmd3.Parameters["lAnswer"].Direction = ParameterDirection.InputOutput;
                    cmd3.Parameters["aText"].Direction = ParameterDirection.InputOutput;
                    cmd3.Parameters["aTable"].Value = "ORGANIZ_CL";
                    cmd3.Parameters["aDirect"].Value = "";
                    cmd3.Parameters["as_New_Due"].Value = new_due;
                    cmd3.Parameters["as_Due"].Value = old_due[i];
                    cmd3.Parameters["as_New_Isn_Node"].Value = new_isn.ToString();
                    cmd3.Parameters["as_Isn_Node"].Value = old_isn[i].ToString();
                    cmd3.Parameters["lAnswer"].Value = "";
                    cmd3.Parameters["aText"].Value = "";
                    cmd3.Parameters["aTable"].Size = 255;
                    cmd3.Parameters["aDirect"].Size = 255;
                    cmd3.Parameters["as_New_Due"].Size = 255;
                    cmd3.Parameters["as_Due"].Size = 255;
                    cmd3.Parameters["as_New_Isn_Node"].Size = 255;
                    cmd3.Parameters["as_Isn_Node"].Size = 255;
                    cmd3.Parameters["lAnswer"].Size = 255;
                    cmd3.Parameters["aText"].Size = 255;
                    #endregion Инициализация процедурі вставки
                    try
                    {
                        cmd3.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Error += "Не удалось объединение: " + new_isn.ToString() + "," + old_isn[i].ToString() +
                            "\nОшибка: " + ex.Message + "\n";
                    }
                    finally
                    {
                        try
                        {
                            #region проставление новых организаций в документах
                            OleDbCommand cmd4 = new OleDbCommand("update " + SqlPrefix + "REF_CORRESP SET DUE_ORGANIZ='" + new_due + "' where DUE_ORGANIZ='" + old_due[i] + "'", connection, trans);
                            cmd4.ExecuteNonQuery();
                            cmd4.Dispose();
                            cmd4 = new OleDbCommand("update " + SqlPrefix + "REF_SEND SET DUE_ORGANIZ='" + new_due + "' where DUE_ORGANIZ='" + old_due[i] + "'", connection, trans);
                            cmd4.ExecuteNonQuery();
                            cmd4.Dispose();
                            cmd4 = new OleDbCommand("update " + SqlPrefix + "REF_SOISP SET DUE_ORGANIZ='" + new_due + "' where DUE_ORGANIZ='" + old_due[i] + "'", connection, trans);
                            cmd4.ExecuteNonQuery();
                            cmd4.Dispose();
                            cmd4 = new OleDbCommand("update " + SqlPrefix + "DEPARTMENT SET ISN_ORGANIZ=" + new_isn + " where ISN_ORGANIZ=" + old_isn[i], connection, trans);
                            cmd4.ExecuteNonQuery();
                            cmd4.Dispose();
                            #endregion проставление новых организаций в документах
                        }
                        finally
                        {
                            try
                            {
                                OleDbCommand cmd5 = new OleDbCommand("delete " + SqlPrefix + "organiz_cl where DUE='" + old_due[i] + "'", connection, trans);
                                cmd5.ExecuteNonQuery();
                                FizDeleted += "," + old_due[i];
                            }
                            catch
                            {
                                OleDbCommand cmd5 = new OleDbCommand("update " + SqlPrefix + "organiz_cl set DELETED=1 where DUE='" + old_due[i] + "'", connection, trans);
                                cmd5.ExecuteNonQuery();
                                LogDeleted += "," + old_due[i];
                            }
                        }
                    }
                    Answer += "lAnswer: " + cmd3.Parameters["lAnswer"].Value.ToString() +
                        "\naText: " + cmd3.Parameters["aText"].Value.ToString() + "\n";
                    cmd3.Dispose();
                }
                prbJoinOrganization.Value++;
                prbJoinOrganization.Update();
            }
            dr1.Close();
            trans.Commit();
            if (Error != "") MessageBox.Show(Error);
            MessageBox.Show(Answer);
            MessageBox.Show(LogDeleted);
            MessageBox.Show(FizDeleted);
        }

        private void btnReportOrganizRefresh_Click(object sender, EventArgs e)
        {
            if ((connection == null) || (connection.State != ConnectionState.Open)) return;
            lsvReportOrganizFolder.Items.Clear();
            string sql = "select CLASSIF_NAME, DUE, ISN_NODE from " + SqlPrefix + "ORGANIZ_CL WHERE (IS_NODE = 0) AND (DELETED = 0) AND (isn_high_node=0) ORDER BY WEIGHT";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ListViewItem item = lsvReportOrganizFolder.Items.Add(dr.GetString(0));
                item.SubItems.Add(dr.GetString(1));
                item.SubItems.Add(dr.GetValue(2).ToString());
            }
            dr.Close();

        }

        private void btnReportOrganizExport_Click(object sender, EventArgs e)
        {
            try
            {
                string sqldue = "";
                for (int i = 0; i < lsvReportOrganizFolder.CheckedItems.Count; i++)
                {
                    sqldue += " or (DUE like '" + lsvReportOrganizFolder.CheckedItems[i].SubItems[1].Text + "%') ";
                }
                sqldue = sqldue.Substring(4);
                System.IO.File.Delete(txtReportOrganizFile.Text);
                treeReportOrganizSub(0, 0, sqldue);
                MessageBox.Show("Завершено");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка:"+ex.Message);
            }
        }

        private void treeReportOrganizSub(Int32 parent_isn, Int32 level, string sqldue)
        {
            string pred = " ";
            for (int i = 0; i < level; i++)
            {
                pred += " ";
            }
            string sql = "select CLASSIF_NAME, ISN_NODE from " + SqlPrefix + "ORGANIZ_CL WHERE (DELETED = 0) AND (isn_high_node="+parent_isn.ToString()+") and ("+sqldue+") ORDER BY WEIGHT";
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                System.IO.File.AppendAllText(txtReportOrganizFile.Text,pred+dr.GetString(0)+"\r\n");
                treeReportOrganizSub(Int32.Parse(dr.GetValue(1).ToString()),level+1,sqldue);
            }
            dr.Close();

        }

    }
}
